Use with Connection Pooling

This documentation is for an older version of ZK. For the latest one, please click here.

Use with Connection Pooling

Connection pooling is a technique of creating and managing a pool of connections that are ready for use by any thread that needs them. Instead of closing a connection immediately, it keeps them in a pool such that the next connect request could be served very efficiently. Connection pooling, in addition, has a lot of benefits, such as control resource usage.

There is no reason not to use connection pooling when developing Web-based applications, including ZK applications.

The concept of using connection pooling is simple: configure, connect and close. The way to connect and close a connection is very similar the ad-hoc approach, while configuration depends on what Web server and database server are in use.

Connect and Close a Connection

After configuring connection pooling (which will be discussed in the following section), you could use JNDI to retrieve an connection as follows.

 import java.sql.Connection;
 import java.sql.SQLException;
 import java.sql.Statement;

 import javax.naming.InitialContext;
 import javax.sql.DataSource;

 import org.zkoss.zul.Window;

 public class MyWindows extends Window {
     private Textbox name, email;
     public void onCreate() {
         //initial name and email
         name = getFellow("name");
         email = getFellow("email");
     }
     public void onOK() throws Exception {
             DataSource ds = (DataSource)new InitialContext()
             .lookup("java:comp/env/jdbc/MyDB");
             //Assumes your database is configured and
             //named as "java:comp/env/jdbc/MyDB"

         Connection conn = null;
         Statement stmt = null;
         try {
             conn = ds.getConnection();
             stmt = conn.prepareStatement("INSERT INTO user values(?, ?)");

             //insert what end user entered into database table
             stmt.setString(1, name.value);
             stmt.setString(2, email.value);

             //execute the statement
             stmt.executeUpdate();
             stmt.close(); stmt = null;
                 //optional because the finally clause will close it
                 //However, it is a good habit to close it as soon as done, especially 
                 //you might have to create a lot of statement to complete a job
         } finally { //cleanup
             if (stmt != null) {
                 try {
                     stmt.close();
                 } catch (SQLException ex) {
                     //(optional log and) ignore
                 }
             }
             if (conn != null) {
                 try {
                     conn.close();
                 } catch (SQLException ex) {
                     //(optional log and) ignore
                 }
             }
         }
     }
 }

Notes:

• It is important to close the statement and connection after use.
• You could access multiple database at the same time by use of multiple connections. Depending on the configuration and J2EE/Web servers, these connections could even form a distributed transaction.

Configure Connection Pooling

The configuration of connection pooling varies from one J2EE/Web/Database server to another. Here we illustrated some of them. You have to consult the document of the server you are using.

Tomcat 5.5 + MySQL

To configure connection pooling for Tomcat 5.5, you have to edit $TOMCAT_DIR/conf/context.xml, and add the following content under the <Context> element. The information that depends on your installation and usually need to be changed is marked in the blue color.

 <!-- The name you used above, must match _exactly_ here!
     The connection pool will be bound into JNDI with the name
     "java:/comp/env/jdbc/MyDB"
 -->
 <Resource name="jdbc/MyDB" username="someuser" password="somepass" 
     url="jdbc:mysql://localhost:3306/test" 
     auth="Container" defaultAutoCommit="false" 
     driverClassName="com.mysql.jdbc.Driver" maxActive="20" 
     timeBetweenEvictionRunsMillis="60000" 
     type="javax.sql.DataSource" />
 </ResourceParams>

Then, in web.xml, you have to add the following content under the <web-app> element as follows.

 <resource-ref>
   <res-ref-name>jdbc/MyDB</res-ref-name>
   <res-type>javax.sql.DataSource</res-type>
   <res-auth>Container</res-auth>
 </resource-ref>

Notes

JBoss + MySQL

The following instructions is based on section 23.3.4.3 of the reference manual of MySQL 5.0.

To configure connection pooling for JBoss, you have to add a new file to the directory called deploy ($JBOSS_DIR/server/default/deploy). The file name must end with "-ds.xml", which tells JBoss to deploy this file as JDBC Datasource. The file must have the following contents. The information that depends on your installation and usually need to be changed is marked in the blue color.

 <datasources>
     <local-tx-datasource>
         <!-- This connection pool will be bound into JNDI with the name
              "java:/MyDB" -->
         <jndi-name>MyDB</jndi-name>
         <connection-url>jdbc:mysql://localhost:3306/test</connection-url>
         <driver-class>com.mysql.jdbc.Driver</driver-class>
         <user-name>someuser</user-name>
         <password>somepass</password>

         <min-pool-size>5</min-pool-size>

         <!-- Don't set this any higher than max_connections on your
          MySQL server, usually this should be a 10 or a few 10's
          of connections, not hundreds or thousands -->

         <max-pool-size>20</max-pool-size>

         <!-- Don't allow connections to hang out idle too long,
          never longer than what wait_timeout is set to on the
          server...A few minutes is usually okay here,
          it depends on your application
          and how much spikey load it will see -->

         <idle-timeout-minutes>5</idle-timeout-minutes>

         <!-- If you're using Connector/J 3.1.8 or newer, you can use
              our implementation of these to increase the robustness
              of the connection pool. -->

         <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
         <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>

     </local-tx-datasource>
 </datasources>

JBoss + PostgreSQL

 <datasources>
     <local-tx-datasource>
     <!-- This connection pool will be bound into JNDI with the name
          "java:/MyDB" -->
     <jndi-name>MyDB</jndi-name>

     <!-- jdbc:postgresql://[servername]:[port]/[database name] -->
     <connection-url>jdbc:postgresql://localhost/test</connection-url>

     <driver-class>org.postgresql.Driver</driver-class>
     <user-name>someuser</user-name>
     <password>somepass</password>
     <min-pool-size>5</min-pool-size>
     <max-pool-size>20</max-pool-size>
     <track-statements>false</track-statements>
     </local-tx-datasource>
 </datasources>